/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.controller;

import com.tamnhat.entity.Category;
import com.tamnhat.entity.ContTemDetail;
import com.tamnhat.entity.ContentTemplate;
import com.tamnhat.entity.Item;
import com.tamnhat.entity.Publisher;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.ResourceBundle;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.faces.model.DataModel;
import javax.faces.model.ListDataModel;
import javax.faces.model.SelectItem;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpSessionEvent;
import javax.servlet.http.HttpSessionListener;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.event.FileUploadEvent;
import org.primefaces.model.DefaultStreamedContent;
import org.primefaces.model.StreamedContent;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class ItemsController implements Serializable{

    private Item currentItem = new Item();
    private String cateId;
    private String pubId;
    private String ctId;
    private Item oldCurrentItem = new Item();
    private DataModel list;
    private List<Item> listItem = new ArrayList<Item>();
    private int typeS;
    private String inputS;
    private List<Category> listCate = new ArrayList<Category>();
    private List<Publisher> listPub = new ArrayList<Publisher>();
    private List<ContentTemplate> listContTem;
    private List<ContTemDetail> listProperty;
    private List<ContTemDetail> listPropertyCurrent;
    private String propertyInput = "";
    private boolean checkDLExcell = false;
    private String linkDownErFile = "";
    private StreamedContent fileEr;
    private String path = "";

    /**
     * Creates a new instance of ItemsController
     */
    public ItemsController() {
        try {      
            ResourceBundle resource = ResourceBundle.getBundle("config");
            String url = resource.getString("urlExcellFile");
            path = url;
            listItem = Item.allItem("");
            list = new ListDataModel(listItem);
            listCate = Category.allCate("");
            listPub = Publisher.allPub("");
            listContTem = ContentTemplate.allCT("");            
            propertyInput = "";
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        }
    }

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    public String getCtId() {
        return ctId;
    }

    public void setCtId(String ctId) {
        this.ctId = ctId;
    }

    public String getCateId() {
        return cateId;
    }

    public void setCateId(String cateId) {
        this.cateId = cateId;
    }

    public String getPubId() {
        return pubId;
    }

    public void setPubId(String pubId) {
        this.pubId = pubId;
    }

    public Item getOldCurrentItem() {
        return oldCurrentItem;
    }

    public void setOldCurrentItem(Item oldCurrentItem) {
        this.oldCurrentItem = oldCurrentItem;
    }

    public List<Item> getListItem() {
        return listItem;
    }

    public void setListItem(List<Item> listItem) {
        this.listItem = listItem;
    }

    public StreamedContent getFileEr() {
        return fileEr;
    }

    public void setFileEr(StreamedContent fileEr) {
        this.fileEr = fileEr;
    }

    public String getLinkDownErFile() {
        return linkDownErFile;
    }

    public void setLinkDownErFile(String linkDownErFile) {
        this.linkDownErFile = linkDownErFile;
    }

    public boolean isCheckDLExcell() {
        return checkDLExcell;
    }

    public void setCheckDLExcell(boolean checkDLExcell) {
        this.checkDLExcell = checkDLExcell;
    }

    public List<ContTemDetail> getListPropertyCurrent() {
        return listPropertyCurrent;
    }

    public void setListPropertyCurrent(List<ContTemDetail> listPropertyCurrent) {
        this.listPropertyCurrent = listPropertyCurrent;
    }

    public String getPropertyInput() {
        return propertyInput;
    }

    public void setPropertyInput(String propertyInput) {
        this.propertyInput = this.propertyInput + ";" + propertyInput;
    }

    public List<ContTemDetail> getListProperty() {
        return listProperty;
    }

    public void setListProperty(List<ContTemDetail> listProperty) {
        this.listProperty = listProperty;
    }

    public String getInputS() {
        return inputS;
    }

    public void setInputS(String inputS) {
        this.inputS = inputS;
    }

    public int getTypeS() {
        return typeS;
    }

    public void setTypeS(int typeS) {
        this.typeS = typeS;
    }

    public Item getCurrentItem() {
        return currentItem;
    }

    public void setCurrentItem(Item currentItem) {
        this.currentItem = currentItem;
    }

    public DataModel getList() {
        return list;
    }

    public void setList(DataModel list) {
        this.list = list;
    }

    public List<Category> getListCate() {
        return listCate;
    }

    public Category cateSelected(){
        for(int i=0;i<listCate.size();i++){
            if(listCate.get(i).getId().equals(cateId)){
                return listCate.get(i);
            }
        }
        return null;
    }
    
    public void setListCate(List<Category> listCate) {
        this.listCate = listCate;
    }

    public List<Publisher> getListPub() {
        return listPub;
    }

    public Publisher pubSelected(){
        for(int i=0;i<listPub.size();i++){
            if(listPub.get(i).getId().equals(pubId)){
                return listPub.get(i);
            }
        }
        return null;
    }
    
    public void setListPub(List<Publisher> listPub) {
        this.listPub = listPub;
    }

    public List<ContentTemplate> getListContTem() {
        return listContTem;
    }
    
    public ContentTemplate ctSelected(){
        for(int i=0;i<listContTem.size();i++){
            if(listContTem.get(i).getId().equals(ctId)){
                return listContTem.get(i);
            }
        }
        return null;
    }

    public void setListContTem(List<ContentTemplate> listContTem) {
        this.listContTem = listContTem;
    }

    //------------------
    public void clearField() {
        currentItem = new Item();
        listProperty = new ArrayList<ContTemDetail>();
        listPropertyCurrent = new ArrayList<ContTemDetail>();
    }

    public String updateAdd() {
        try {
            clearField();
            ctId = listContTem.get(0).getId();
            listProperty = ContTemDetail.allCTDNoLike(listContTem.get(0).getId());
            for (int i = 0; i < listProperty.size(); i++) {
                listProperty.get(i).setId("");
            }
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        }
        return "";
    }

    public String updateProperty() {
        try {
            listProperty = ContTemDetail.allCTDNoLike(ctId);
            int i = listProperty.size();
            if (i > 0) {
                for (int j = 0; j < listProperty.size(); j++) {
                    listProperty.get(j).setId("");
                }
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Có " + i + " bản ghi được tìm thấy!"));
            } else {
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Không có bản ghi nào được tìm thấy!"));
            }
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        }
        return "";
    }

    public String updatePropertyEdit() {
        try {
            if (ContentTemplate.getNameById(ctId).equalsIgnoreCase(currentItem.getMediaType().getName())) {
                listProperty = listPropertyCurrent;
            } else {
                listProperty = ContTemDetail.allCTDNoLike(ctId);
                int i = listProperty.size();
                if (i > 0) {
                    for (int j = 0; j < listProperty.size(); j++) {
                        listProperty.get(j).setId("");
                    }
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Có " + i + " bản ghi được tìm thấy!"));
                } else {
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Không có bản ghi nào được tìm thấy!"));
                }
            }
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        }
        return "";
    }

    public String updateEdit() {
        try {

            currentItem = (Item) list.getRowData();
            oldCurrentItem = currentItem;
            cateId = currentItem.getCategoryName().getIdByName();                        
            pubId = currentItem.getPublisherName().getIdByName();
            ctId = currentItem.getMediaType().getId();
            
            listProperty = ContTemDetail.getPropertyByIdItem(currentItem.getId());
            
            List<ContTemDetail> list = new ArrayList<ContTemDetail>();
            list = ContTemDetail.allCTDNoLike(currentItem.getMediaType().getIdByName());
            for(int i=0;i<list.size();i++){
                list.get(i).setId("");
                for(int j=0;j<listProperty.size();j++){
                    if(list.get(i).getPropertyName().equals(listProperty.get(j).getPropertyName())){
                        list.get(i).setId(listProperty.get(j).getId());
                    }
                }
            }                        
            listProperty = list;
            listPropertyCurrent = listProperty;
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        }
        return "";
    }

    public boolean checkInput() {
        if (currentItem.getName() == null && currentItem.getName().equalsIgnoreCase("")) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Tên Item không được bỏ trống!"));
            return false;
        }
        return true;
    }

    public String search() {
        try {
            if (inputS == null) {
                inputS = "";
            }

            if (typeS == 0) {
                listItem = Item.allItem("and LOWER(a.name) like LOWER('%" + inputS + "%') ");
            }
            if (typeS == 1) {
                listItem = Item.allItem("and LOWER(b.name) like LOWER('%" + inputS + "%') ");
            }
            if (typeS == 2) {
                listItem = Item.allItem("and LOWER(c.name) like LOWER('%" + inputS + "%') ");
            }
            if (typeS == 3) {
                listItem = Item.allItem("and LOWER(a.media_type) like LOWER('%" + inputS + "%') ");
            }
            list = new ListDataModel(listItem);
            int i = list.getRowCount();
            if (i > 0) {
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Có " + i + " bản ghi được tìm thấy!"));
            } else {
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Không có bản ghi nào được tìm thấy!"));
            }
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        }
        return "";
    }

    public String insert() {
        Connection cn = null;
        try {
            cn = AppServer.getConnection();
            if (checkInput()) {
                currentItem.setCategoryName(cateSelected());
                currentItem.setPublisherName(pubSelected());
                currentItem.setMediaType(ctSelected());                
                if (currentItem.insert()) {                                        
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Thêm bản ghi thành công!"));
                    for (int j = 0; j < listProperty.size(); j++) {
                        if (listProperty.get(j).getId() != null) {
                            if (currentItem.insertProperty(listProperty.get(j).getPropertyName(), listProperty.get(j).getId(), cn)) {
                                if(!listProperty.get(j).getId().equalsIgnoreCase("")) FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Cập nhật thành công property " + listProperty.get(j).getPropertyName() + "!"));
                            } else {
                                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Cập nhật thất bại property " + listProperty.get(j).getPropertyName() + "!"));
                            }
                        }
                    }
                    listItem.add(currentItem);
                } else {                    
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Xảy ra lỗi trong quá trình thêm bản ghi"));
                }
            }
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        } finally {
            clearField();
            Database.closeObject(cn);
        }
        return "";
    }

    public String remove() {
        try {
            currentItem = (Item) list.getRowData();
            if (currentItem.remove()) {
                listItem.remove(currentItem);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Xóa bản ghi thành công"));
            } else {
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Xảy ra lỗi trong quá trình xử lí!"));
            }
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        }
        return "";
    }

    public String update() {
        Connection cn = null;
        try {
            cn = AppServer.getConnection();
            if (checkInput()) {
                currentItem.setCategoryName(cateSelected());
                currentItem.setPublisherName(pubSelected());
                if (currentItem.update()) {            
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Cập nhật bản ghi thành công!"));
                    for (int j = 0; j < listProperty.size(); j++) {
                        if (listProperty.get(j).getId() != null) {
                            if (currentItem.updateProperty( listProperty.get(j).getPropertyName(), listProperty.get(j).getId(), cn)) {
                                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Cập nhật thành công property " + listProperty.get(j).getPropertyName() + "!"));
                            } else {
                                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Cập nhật thất bại property " + listProperty.get(j).getPropertyName() + "!"));
                            }
                        }
                    }
                } else {
                    currentItem = oldCurrentItem;
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Cập nhật bản ghi thất bại!"));
                }
            }
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("ERROR: " + e.getMessage()));
            e.printStackTrace();
        } finally {
            clearField();
            Database.closeObject(cn);
        }
        return "";
    }

    //upload file
    public void upload(FileUploadEvent event) {
        String path = this.path;

        SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");
        String name = fmt.format(new Date())
                + event.getFile().getFileName().substring(
                event.getFile().getFileName().lastIndexOf('.'));
        FacesMessage msg = new FacesMessage("Success! ", event.getFile().getFileName() + " is uploaded.");
        FacesContext.getCurrentInstance().addMessage(null, msg);
        // Do what you want with the file         
        String separator = System.getProperty("file.separator");
        try {
            copyFile(event.getFile().getFileName() + "_" + name, event.getFile().getInputstream());
            ArrayList<com.tamnhat.entity.Error> list = readXLSXFile(path + separator + event.getFile().getFileName() + "_" + name, currentItem.getMediaType().getId());
            writeXLSXFile(path + separator + "Error_" + event.getFile().getFileName() + "_" + name, list);
        } catch (Exception e) {
            e.printStackTrace();
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(e.getMessage()));
        }
    }

    public void copyFile(String fileName, InputStream in) throws Exception {
        String separator = System.getProperty("file.separator");
        String path = this.path;
        // write the inputStream to a FileOutputStream
        OutputStream out = new FileOutputStream(new File(path + separator + fileName));

        int read = 0;
        byte[] bytes = new byte[1024];

        while ((read = in.read(bytes)) != -1) {
            out.write(bytes, 0, read);
        }

        in.close();
        out.flush();
        out.close();

//        System.out.println("New file created!");
    }

    public ArrayList<com.tamnhat.entity.Error> readXLSXFile(String url, String mediaTypeId) throws Exception {        
        InputStream ExcelFileToRead = new FileInputStream(url);
        XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row;
        XSSFCell cell;
        XSSFCell cellHeader;
        ArrayList<String> listHeader = new ArrayList<String>();
        ArrayList<com.tamnhat.entity.Error> listEror = new ArrayList<com.tamnhat.entity.Error>();

        boolean stop = false;

        Iterator rows = sheet.rowIterator();
        row = (XSSFRow) rows.next();
        Iterator cellsHeader = row.cellIterator();
//        System.out.println("Ten cot: ");
        //lay ra danh sach ten cot
        while (cellsHeader.hasNext()) {
            cellHeader = (XSSFCell) cellsHeader.next();
            if (evaluator.evaluateInCell(cellHeader).getCellType() == XSSFCell.CELL_TYPE_STRING) {
//                System.out.print(cellHeader.getStringCellValue() + " | ");
                listHeader.add(cellHeader.getStringCellValue());
            }
        }

        //kiem tra xem co ton tai Header ko?
        ArrayList<String> listHeaderFromDB = listTemDetail(mediaTypeId);
        if (listHeader.size() == 0) {
            com.tamnhat.entity.Error er = new com.tamnhat.entity.Error();
            er.setRow(-1);
            ArrayList<String> listEr = new ArrayList<String>();
            listEr.add("Hãy chọn đúng template file xlsx quy định");
            er.setColName(listEr);
            listEror.add(er);
            return listEror;
        }
        //kiem tra xem co thieu cot nao khong?
        com.tamnhat.entity.Error erHeader = new com.tamnhat.entity.Error();
        erHeader.setRow(1);
        if (!listHeader.get(0).toLowerCase().contains("item-name")) {
            erHeader.addColName("Item-Name(*)");
        }
        if (!listHeader.get(1).toLowerCase().contains("category")) {
            erHeader.addColName("Category(*)");
        }
        if (!listHeader.get(2).toLowerCase().contains("publisher")) {
            erHeader.addColName("Publisher(*)");
        }
        if (!listHeader.get(3).toLowerCase().contains("content_type_category")) {
            erHeader.addColName("Content_type_category(*)");
        }
        for (int i = 0; i < listHeaderFromDB.size(); i++) {
            if (listHeader.size() > i + 4) {
                if (!listHeader.get(i + 4).toLowerCase().contains(listHeaderFromDB.get(i).toLowerCase())) {
                    erHeader.addColName(listHeaderFromDB.get(i));
                }
            } else {
                erHeader.addColName(listHeaderFromDB.get(i));
            }
        }
        //neu ton tai loi header dung lai luon
        if (erHeader.getColName().size() > 0) {
            listEror.add(erHeader);
            return listEror;
        }
        //kiem tra den content sau khi hoan tat kiem tra header
        ContentTemplate ct = new ContentTemplate();
        ct.setStatus("1");
        Properties p;
        int countInsert = 0;
        int checkError = 0;
        while (rows.hasNext() && !stop) {
            p = new Properties();
            boolean checkInsert = true;
            row = (XSSFRow) rows.next();
//            Iterator cells = row.cellIterator();
            int i = 0;
            com.tamnhat.entity.Error er = new com.tamnhat.entity.Error();
            er.setRow(row.getRowNum() + 1);

            for (int j = 0; j < listHeader.size(); j++) {
                cell = row.getCell(j);                
                if (cell == null || evaluator.evaluateInCell(cell).toString().equalsIgnoreCase("")) {
//                    System.out.print("null" + "|");
                    i++;
                    if (listHeader.get(j).contains("*")) {
                        er.addColName(listHeader.get(j));
                        checkInsert = false;
                    } else {
                        p.put(listHeader.get(j), "");
                    }
                } else if ( evaluator.evaluateInCell(cell).getCellType() == XSSFCell.CELL_TYPE_STRING) {
//                    System.out.print(cell.getStringCellValue() + "|");
                    p.put(listHeader.get(j), cell.getStringCellValue());
                } else {
                    er.addColName(listHeader.get(j) + " sai kiểu dữ liệu");
                    checkInsert = false;
                }
            }

            if (i == listHeader.size()) {
                stop = true;
            } else {
                listEror.add(er);
            }
            if (checkInsert == true) {
                Item item = new Item();
                Category c = new Category();
                Publisher pub = new Publisher();
                item.setName(p.getProperty(listHeader.get(0)));
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue() == null || row.getCell(1).getStringCellValue().equalsIgnoreCase("")) {                                        
                    item.setCategoryName(c);
                } else {                    
                    c.setId(Category.getIdByName(row.getCell(1).getStringCellValue()));
                    item.setCategoryName(c);
                }
                if (row.getCell(2) == null || row.getCell(2).getStringCellValue() == null || row.getCell(2).getStringCellValue().equalsIgnoreCase("")) {
                    item.setPublisherName(pub);
                } else {                    
                    pub.setId(Publisher.getIdByName(row.getCell(2).getStringCellValue()));
                    item.setPublisherName(pub);
                }
                ContentTemplate content = new ContentTemplate();
                content.setName(ContentTemplate.getNameById(mediaTypeId));
                item.setMediaType(content);
                item.setContent_type(row.getCell(3).getStringCellValue());
                Connection cn = null;
                try {
                    cn = AppServer.getConnection();
                    if (item.insert()) {
                        for (int in = 4; in < listHeader.size(); in++) {
                            item.insertProperty(listHeaderFromDB.get(in - 4), p.getProperty(listHeader.get(in)), cn);
                        }
                        countInsert++;
                    } else {
                        checkError++;
                    }
                } finally {
                    Database.closeObject(cn);
                }

            }
        }
//        System.out.println("them thanh cong " + countInsert + " ban ghi!");
        if (countInsert > 0) {
            FacesMessage msg = new FacesMessage("Thêm thành công " + countInsert + " bản ghi!");
            FacesContext.getCurrentInstance().addMessage(null, msg);
        }
        if (checkError > 0) {
            FacesMessage msg = new FacesMessage("Xảy ra lỗi với " + checkError + " bản ghi!");
            FacesContext.getCurrentInstance().addMessage(null, msg);
        }
        //     
        return listEror;
    }

    public void writeXLSXFile(String url, ArrayList<com.tamnhat.entity.Error> listError) throws IOException {
        ArrayList<String> list = new ArrayList<String>();
        if (listError.size() <= 0) {
            return;
        }

        for (int i = 0; i < listError.size(); i++) {
            com.tamnhat.entity.Error er = new com.tamnhat.entity.Error();
            er = listError.get(i);
            String colName = "";
            if (er.getColName().size() > 0) {
                for (int j = 0; j < er.getColName().size(); j++) {
                    colName += er.getColName().get(j);
                }
            }
            if (er.getColName().size() > 0 && er.getRow() > 1) {
                list.add("Dòng " + er.getRow() + ", Cột " + colName + " không được để trống!");
            } else if (er.getRow() == 1) {
                list.add("Dòng " + er.getRow() + " thiếu cột " + colName);
            } else {
                list.add(colName);
            }
        }

        String excelFileName = url;//name of excel file

        String sheetName = "Sheet1";//name of sheet

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(sheetName);

        //iterating r number of rows
        boolean checkWrite = false;
        for (int r = 0; r < list.size(); r++) {
            XSSFRow row = sheet.createRow(r);

            //iterating c number of columns
            XSSFCell cell = row.createCell(0);
            if (!list.get(r).toString().equalsIgnoreCase("")) {
                cell.setCellValue(list.get(r).toString());
                checkWrite = true;
            }
        }
        if (checkWrite) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Có lỗi xảy ra trong file xlsx!"));
            FileOutputStream fileOut = new FileOutputStream(excelFileName);
//write this workbook to an Outputstream.
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            checkDLExcell = true;
//            String path = this.path;
//            String realUrl = url.replace(path, "");
//            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext()).getResourceAsStream(realUrl);            
            InputStream stream = new FileInputStream(new File(excelFileName)); 
            String separator = System.getProperty("file.separator");
            String fileName = excelFileName.replace(path + separator, "");
            fileEr = new DefaultStreamedContent(stream, "application/xlsx", fileName);
        }
//        System.out.println("Completed");
    }

    public ArrayList<String> listTemDetail(String temId) throws Exception {
        ArrayList<String> list = new ArrayList<String>();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = " select * from md_content_template_details "
                + " where content_template_id = " + temId + " "
                + " order by property_name";
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                list.add(rs.getString(3));
            }
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return list;
    }

    public String updateAddNhiu() {
        currentItem.setMediaType(ctSelected());
        checkDLExcell = false;
        return "";
    }
}
